﻿using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.IO;

namespace WPFExcelReport
{
    class YCAABAPPReport : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        public OracleConnection OracleDB;
        public OracleCommand orlReadCommand = new OracleCommand();
        public IntexReptServBLClient proxy;
        private Dictionary<string, object> dicParamater;
        public DataSet dsResult;
        public DataSet setUI = new DataSet();
        string ABPAUFFKRS;
        string ABPAUFFLFD;
        string ABPAUFFPOS;
        string WBBTP;
        string DocType;
        string CellName = "";
        string CellValue = "";
        string strCompany;
        string strExcelFile;
        string Today
        {
            get
            {
                DateTime now = DateTime.Now;
                return now.Year.ToString() + Right("00" + now.Month.ToString(), 2) + Right("00" + now.Day.ToString(), 2);
            }
        }
        #endregion
        public YCAABAPPReport()
            : base()
        {
        }
        public YCAABAPPReport(Dictionary<string, object> dicParamater)
            : base(dicParamater)
        {
            DocType = dicParamater["DocType"].ToString().Trim();
            strCompany = dicParamater["Company"].ToString().Trim();
            strExcelFile = dicParamater["ExcelFile"].ToString().Trim();
        }
        public override void WriteExcelReport(string fileType)
        {
            bool IsFTP = false;
            try
            {

                strExcelFile = @"SDT\SDTStandard.xls";
                DataRow dwVKPOS = dsSource.Tables["ABKOPF"].Rows[0];
                string KRS = dwVKPOS["ABKAUFKRS"].ToString().Trim();
                string LFD = dwVKPOS["ABKAUFLFD"].ToString().Trim();
                long flfd = 0;
                flfd = System.Convert.ToInt64(LFD);
                string newfilename = KRS.Trim() + flfd.ToString();
                exAgent.CheckWorkBook(newfilename);
                LocalFile(KRS + LFD);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInEXCEL()
        {
            try
            {
                string value = "";
                DataRow row = null;
                int x = 1, y = 1;
                row = dsSource.Tables["ABKOPF"].Rows[0];
                value = row["ABKAUFKRS"].ToString() + "-" + row["ABKAUFLFD"].ToString();
                x = 3; y = 35;
                exAgent.SetCellValue(x, y, value);

                x = 4; y = 6;
                exAgent.SetCellValue(x, y, Today);
                if (dsSource.Tables.Contains("LIEFDAT"))
                {
                    if (dsSource.Tables["LIEFDAT"].Rows.Count > 0)
                    {
                        row = dsSource.Tables["LIEFDAT"].Rows[0];
                        value = row["LSNAME1"].ToString().Trim() + row["LSNAME2"].ToString();
                        value = GetGB2312Value(value);
                        x = 3; y = 6;
                        exAgent.SetCellValue(x, y, value);
                        value = row["LSSTRASSE1"].ToString();
                        value = GetGB2312Value(value);
                        x = 3; y = 16;
                        exAgent.SetCellValue(x, y, value);
                    }
                }
                int rown = -1;
                foreach (DataRow dwABPOS in dsSource.Tables["ABPOS"].Rows)
                {

                    string art = dwABPOS["ABPART"].ToString();
                    string var = dwABPOS["ABPVAR"].ToString();

                    value = dwABPOS["ABPLIEFDAT"].ToString();
                    x = 4; y = 35;
                    exAgent.SetCellValue(x, y, value);
                    value = dwABPOS["ABPFILLER1"].ToString();
                    x = 44; y = 1;
                    exAgent.SetCellValue(x, y, value);

                    var = Left(var, 2);
                    string color = dwABPOS["ABPFARBE"].ToString();
                    string finish = dwABPOS["ABPBESCHICH"].ToString();
                    string design = dwABPOS["ABPDESS"].ToString();
                    string colorway = dwABPOS["ABPCOLORIT"].ToString();

                    var query = from rows in dsSource.Tables["WASTAMM"].AsEnumerable()
                                where rows["WAART"].ToString() == art
                                & rows["WAVAR"].ToString() == var
                                select rows;
                    foreach (DataRow rw in query)
                    {
                        x = 6; y = 6;
                        exAgent.SetCellValue(x, y, art);//成品品名
                        value = rw["WAKAART"].ToString().Trim();
                        x = 6; y = 26;
                        exAgent.SetCellValue(x, y, value);//坯布品名
                        value = rw["WARBEM"].ToString().Trim();//WS03的备注信息，经纬纱都写在一起即可。
                        x = 9; y = 6;
                        exAgent.SetCellValue(x, y, value);
                        value = System.Math.Round(System.Convert.ToDouble(rw["WAFBRTL"].ToString()) * 0.1, 3).ToString() + "cm";
                        x = 12; y = 6;
                        exAgent.SetCellValue(x, y, value);//成品幅宽：
                        value = rw["WAFGRQM"].ToString() + "g/m2";
                        x = 12; y = 16;
                        exAgent.SetCellValue(x, y, value);//成品克重：
                        value = System.Math.Round(System.Convert.ToDouble(rw["WAFFDDIK"].ToString()) * 0.01, 3).ToString() + "/cm";
                        x = 12; y = 26;
                        exAgent.SetCellValue(x, y, value);//成品经密：
                        value = System.Math.Round(System.Convert.ToDouble(rw["WAFFDDIS"].ToString()) * 0.01, 3).ToString() + "/cm";
                        x = 12; y = 35;
                        exAgent.SetCellValue(x, y, value);//成品纬密：
                    }
                    #region=====composition=======
                    string composition = "";
                    foreach (DataRow rw in dsSource.Tables["STUELI"].Rows)
                    {
                        string sg = rw["STUELISASB"].ToString().Trim();
                        string data = rw["STUELIDATEN"].ToString();
                        string comp = GetGB2312Value(data.Substring(4, 20)).Trim();
                        double ratio = System.Convert.ToDouble(Left(data, 4)) * 0.1;
                        composition += ratio + "%" + sg + comp;
                    }
                    x = 8; y = 6;
                    exAgent.SetCellValue(x, y, composition);
                    #endregion


                    rown++;
                    #region====Color===================
                    query = from rows in dsSource.Tables["WAFARBEN"].AsEnumerable()
                            where rows["WAFART"].ToString() == art
                            & rows["WAFVAR"].ToString() == var
                             & (rows["WAFZUART"].ToString() == "F" || rows["WAFZUART"].ToString() == "C")
                           & rows["WAFZUSATZ"].ToString() == color
                            select rows;

                    foreach (DataRow rw in query)
                    {
                        exAgent.SetCellValue(14, 6 + rown * 8, color);
                        value = rw["WAFBEZ"].ToString();
                        value = GetGB2312Value(value);
                        exAgent.SetCellValue(15, 6 + rown * 8, value);
                        value = rw["WAFMUDESS"].ToString();
                        exAgent.SetCellValue(16, 6 + rown * 8, value);
                        break;
                    }
                    value = System.Math.Round(System.Convert.ToDouble(dwABPOS["ABPSOLLMENGE1"].ToString()) * 0.01, 3).ToString();
                    /*+
                        "m("+
                         System.Math.Round(System.Convert.ToDouble(dwABPOS["ABPSOLLMENGE5"].ToString()) * 0.01, 3).ToString()
                         +"kg)";*/
                    exAgent.SetCellValue(18, 6 + rown * 8, value);
                    #endregion

                    #region====print============
                    /*
                    //ABPDESS	ABPCOLORIT
                    query = from rows in dsSource.Tables["WAFARBEN"].AsEnumerable()
                            where rows["WAFART"].ToString() == art
                            & rows["WAFVAR"].ToString() == var
                            & rows["WAFZUART"].ToString() == "D"
                            & rows["WAFZUSATZ"].ToString() == design
                            & rows["WAFCOLORIT"].ToString() == colorway
                            select rows;

                    foreach (DataRow rw in query)
                    {
                        exAgent.SetCellValue(12 + rown * 2, 13, design + "/" + colorway);
                        value = rw["WAFBEZ"].ToString();
                        value = GetGB2312Value(value);
                        exAgent.SetCellValue(12 + rown * 2 + 1, 13, value);
                        value = rw["WAFMUDESS"].ToString();
                    }
                    */
                    #endregion

                    #region======Finish========
                    query = from rows in dsSource.Tables["WAFARBEN"].AsEnumerable()
                            where rows["WAFART"].ToString() == art
                            & rows["WAFVAR"].ToString() == var
                             & rows["WAFZUART"].ToString() == "B"
                           & rows["WAFZUSATZ"].ToString() == finish
                            select rows;
                    foreach (DataRow rw in query)
                    {
                        //exAgent.SetCellValue(12 + rown * 2, 20, color);
                        value = rw["WAFBEZ"].ToString();
                        value = GetGB2312Value(value);
                        exAgent.SetCellValue(19, 6 + rown * 8, value);
                        break;
                    }
                    #endregion

                    #region====price=======
                    /*
                    value = dwABPOS["ABPSOLLMENGE6"].ToString();
                    value = System.Math.Round(System.Convert.ToDouble(value) * 0.01, 3).ToString()+"kg";
                    exAgent.SetCellValue(12 + rown * 2, 27, value);
                    query = from rows in dsSource.Tables["ABPR"].AsEnumerable()
                            where rows["ARAUFKRS"].ToString() == dwABPOS["ABPAUFKRS"].ToString()
                            & rows["ARAUFLFD"].ToString() ==dwABPOS["ABPAUFLFD"].ToString()
                            & rows["ARAUFPOS"].ToString() == dwABPOS["ABPAUFPOS"].ToString()
                            select rows;
                    foreach (DataRow rw in query)
                    {
                        value = rw["ARWERT"].ToString();
                        value = System.Math.Round(System.Convert.ToDouble(value) * 0.001, 3).ToString()+"";
                        exAgent.SetCellValue(12 + rown * 2, 32, value);
                    }
                     *   */
                    #endregion
                }
                FillInQPPLAN(dsSource.Tables["WAPROF"]);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInQPPLAN(DataTable tbWAPROF)
        {
            try
            {
                int rowstart = 36;
                int x = 1;
                int j = 0;
                int y = 1;
                foreach (DataRow row in tbWAPROF.Rows)
                {
                    string term = "";
                    term = row["WAPANFART"].ToString();
                    var query = from rows in dsSource.Tables["QPMERKM"].AsEnumerable()
                                where rows["QPMMERKM"].ToString() == term
                                select rows;
                    if (j == 0)
                        y = 1;
                    if (j == 1)
                        y = 21;
                    foreach (DataRow rowx in query)
                    {

                        exAgent.SetCellValue(rowstart + x, y, GetGB2312Value(rowx["QPMKURZB"].ToString()));

                        string bem = GetGB2312Value(row["WAPBEM"].ToString()).Trim();
                        string me = GetGB2312Value(row["WAPME"].ToString());
                        if (bem.StartsWith("00"))
                        {
                            int first1 = bem.IndexOfAny(new char[] { '1', '2', '3', '4', '5', '6', '7', '8', '9' });
                            if (first1 > 0)
                            {
                                bem = bem.Substring(first1);
                                bem = (System.Convert.ToDouble(bem) * 0.0001).ToString();
                            }
                        }
                        exAgent.SetCellValue(rowstart + x, y + 7, bem + me);
                    }
                    j++;
                    if (j == 2)
                    {
                        x++;
                        j = 0;
                    }
                    //21,30
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }

        }
        private void HideNullRows(int pages)
        {
            try
            {
                exAgent.HideRows((pages + 1) * 25 + 1, 1, 500, 999);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private void CheckDirectory(string root, string sub, string year, string month, string day)
        {
            try
            {
                string drlocal = root;
                string dr0 = "";

                #region========================LOCAL=====================
                drlocal = Path.Combine(PathHelper.ApplicationPath, @"Report");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);

                drlocal = Path.Combine(drlocal, "Sales");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
                #endregion
                drlocal = root;
                drlocal = Path.Combine(drlocal, sub);
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private string CreateFile(string orderno)
        {
            string filename = "";
            string sheetName = "";
            string Standard = "AB05DOC";
            string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\SDT\SDTStandard.xls");
            try
            {
                string pwd = "";
                sheetName = orderno;
                exAgent.Open(filePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                string newfilename = orderno;
                exAgent.CreateNewSheet(newfilename, Standard, orderno.Replace(".xls", ""), pwd);
                filename = exAgent.FileName;
                FillInEXCEL();
                string localFile = exAgent.FileName;
                exAgent.Save();
                exAgent.Close();
                //System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return filename;
        }

        private void LocalFile(string ordernumer)
        {
            DataRow dwABKOPF = dsSource.Tables["ABKOPF"].Rows[0];
            string fileabsname = ordernumer + ".xls";
            string localFile = @"C:\Windows\Temp\" + fileabsname;
            string tempfile = "";
            try
            {
                tempfile = CreateFile(fileabsname);
                localFile = @"C:\IntexReport\Production\" + fileabsname;
                if (System.IO.File.Exists(localFile))
                {
                    System.IO.File.Delete(localFile);
                }
                File.Move(tempfile, localFile);
                System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
    }
}